﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.BasicList
{
    public partial class panel_UnitExchange : UserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        private SqlConnection masterConnection;

        public panel_UnitExchange()
        {
            InitializeComponent();

            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.masterConnection = new SqlConnection(mySetting.DB2ConnectionString);

            LoadData();

            this.UnitExchange.DoubleClick += Customer_DoubleClick;
            this.UnitExchange.PopupMenuShowing += Customer_PopupMenuShowing;
            this.unitExchangeContext.ItemClicked += customerContext_ItemClicked;
        }

        private void callEditForm()
        {
            DataRow row = UnitExchange.GetFocusedDataRow();
            frm_Edit_UnitExchange editForm = new frm_Edit_UnitExchange();
            editForm.part_no = row[0].ToString();
            if (editForm.LoadData())
            {                
                editForm.ShowDialog();
                LoadData();
            }
            else
            {
                MessageBox.Show("Cannot load data for selected record");
            }
        }

        void Customer_DoubleClick(object sender, EventArgs e)
        {
            callEditForm();  
        }

        void customerContext_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {
            this.unitExchangeContext.Hide();            
            switch (e.ClickedItem.Name)
            {
                case "importSelection":
                    importDatabase();
                    break;
                case "addSelection":
                    frm_Add_UnitExchange add_form = new frm_Add_UnitExchange();
                    add_form.ShowDialog();
                    LoadData();
                    break;
                case "editSelection":
                    callEditForm();
                    break;
                case "deleteSelection":
                    deleteRecord();
                    break;
                case "refreshSelection":
                    LoadData();
                    break;
            }
        }

        void Customer_PopupMenuShowing(object sender, DevExpress.XtraGrid.Views.Grid.PopupMenuShowingEventArgs e)
        {
            this.unitExchangeContext.Show(MousePosition);
        }

        ///////////////////////////////////
        /// Load Data From Local Database
        ///////////////////////////////////
        private void LoadData()
        {
            DataSet sourceDataSet = new DataSet();
            INISettings mySettings = new INISettings();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySettings.SQLConnectionString;
            string sql = "SELECT t1.[PartNo], t1.[UsingUnit], t1.[BuyingUnit], t3.UnitID AS IQUnit,"
                + " t1.[Using/IQEx], t1.[Buying/IQEx]"
                + " FROM [222_PartExchange] AS t1 LEFT OUTER JOIN"
                + " [222_Part] AS t2 ON t1.[PartNo] = t2.[PartNo] LEFT OUTER JOIN"
                + " [222_IQ] AS t3 ON t2.[IQID] = t3.[IQID]";
            sourceDataSet = mySQLManagement.SQL_Query(sql);
            if (sourceDataSet != null)
            {
                gridUnitExchange.DataSource = sourceDataSet.Tables[0];                
            }
            else
            {
                MessageBox.Show("There is no data for UnitExchange");
                DataTable newTable = new DataTable();
                newTable.Columns.Add("PartNo", typeof(string));
                newTable.Columns.Add("UsingUnit", typeof(string));
                newTable.Columns.Add("BuyingUnit", typeof(string));
                newTable.Columns.Add("IQUnit", typeof(string));
                newTable.Columns.Add("Using/IQEx", typeof(float));
                newTable.Columns.Add("Buying/IQEx", typeof(float));
                gridUnitExchange.DataSource = newTable;
            }
            UnitExchange.Columns[0].Caption = "Part No";
            UnitExchange.Columns[1].Caption = "Using Unit";
            UnitExchange.Columns[2].Caption = "Buying Unit";
            UnitExchange.Columns[3].Caption = "IQ Unit";
            UnitExchange.Columns[4].Caption = "Using/IQ Exchange";
            UnitExchange.Columns[5].Caption = "Buying/IQ Exchange";
            UnitExchange.BestFitColumns();
        }

        private void buttonRefresh_Click(object sender, EventArgs e)
        {
            LoadData();
        }


        private void buttonEdit_Click(object sender, EventArgs e)
        {
            callEditForm();  
        }

        private void buttonImport_Click(object sender, EventArgs e)
        {
            importDatabase();
        }


        ////////////////////////////////////
        /// Update database From Excel
        ////////////////////////////////////
        private void importDatabase()
        {
            OleDbDataAdapter adp;
            DataTable excel_data = new DataTable();
            
            bool have_data = false;

            importFile.Filter = "Excel 2007-2013 (.xlsx)|*.xlsx";
            importFile.FilterIndex = 1;
            importFile.Multiselect = false;
            DialogResult userClickOK = importFile.ShowDialog();
            if (userClickOK == DialogResult.OK)
            {
                OleDbConnection excelConnection = null;
                try
                {
                    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                        importFile.FileName + ";Extended Properties=Excel 12.0;Persist Security Info=False";
                    excelConnection = new OleDbConnection(excelConnectionString);
                    OleDbCommand cmd = new OleDbCommand("Select [PartNo], [UsingUnit], [BuyingUnit], [Using/IQEx],"
                        + " [Buying/IQEx]"
                        + " FROM [Sheet1$]", excelConnection);
                    excelConnection.Open();
                    adp = new OleDbDataAdapter(cmd);
                    adp.Fill(excel_data);
                    have_data = true;
                    excelConnection.Close();
                }
                catch
                {
                    if (excelConnection.State == ConnectionState.Open)
                    {
                        excelConnection.Close();
                    }
                    have_data = false;
                    MessageBox.Show("Cannot import data from file. Please check your file");
                }
            }            

            if (have_data)
            {

                DataSet unit_exchange = new DataSet();
                string sql = null;
                SqlDataAdapter adapter;
                DataTable exchange_local;
                try
                {
                    //Get Data From Local Database
                    myConnection.Open();
                    sql = "SELECT * FROM [222_PartExchange]";
                    SqlCommand cmd = new SqlCommand(sql, myConnection);
                    adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    adapter.Fill(unit_exchange, "222_PartExchange");
                    exchange_local = unit_exchange.Tables["222_PartExchange"];
                    int add = 0;
                    int edit = 0;
                    foreach (DataRow excel_row in excel_data.Rows)
                    {
                        DataRow[] current_row = exchange_local.Select("PartNo='" + excel_row["PartNo"].ToString() + "'");
                        if (current_row.Length > 0)
                        {
                            foreach (DataRow local_row in current_row)
                            {
                                local_row.BeginEdit();
                                local_row["UsingUnit"] = excel_row["UsingUnit"].ToString();
                                local_row["BuyingUnit"] = excel_row["BuyingUnit"].ToString();
                                local_row["Using/IQEx"] = float.Parse(excel_row["Using/IQEx"].ToString());
                                local_row["Buying/IQEx"] = float.Parse(excel_row["Buying/IQEx"].ToString());
                                local_row.EndEdit();
                                edit++;
                            }
                        }
                        else
                        {
                            DataRow new_row = exchange_local.NewRow();
                            new_row["PartNo"] = excel_row["PartNo"].ToString();
                            new_row["UsingUnit"] = excel_row["UsingUnit"].ToString();
                            new_row["BuyingUnit"] = excel_row["BuyingUnit"].ToString();
                            new_row["Using/IQEx"] = float.Parse(excel_row["Using/IQEx"].ToString());
                            new_row["Buying/IQEx"] = float.Parse(excel_row["Buying/IQEx"].ToString());
                            exchange_local.Rows.Add(new_row);
                            add++;
                        }
                    } 


                    SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(adapter);
                    //Update to Local Database
                    adapter.Update(unit_exchange, "222_PartExchange");

                    adapter.Dispose();
                    cmd.Dispose();
                    myConnection.Close();                    

                    LoadData();

                    MessageBox.Show("Database has been updated successfull.");

                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                    //MessageBox.Show("Cannot connect to AS400");
                }
            }
        }

        private void buttonAdd_Click(object sender, EventArgs e)
        {
            frm_Add_UnitExchange add_form = new frm_Add_UnitExchange();
            add_form.ShowDialog();
            LoadData();
        }

        private void buttonDelete_Click(object sender, EventArgs e)
        {
            deleteRecord();
        }

        private void deleteRecord()
        {
            int[] rows = UnitExchange.GetSelectedRows();
            if (rows.Length <= 0)
            {
                MessageBox.Show("There is no selected record to delete");
            }
            else
            {
                if (MessageBox.Show("Are you really to delete selected records now?", "Confirm delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {
                    string[] id_list = new string[rows.Length];
                    for (int i = 0; i < rows.Length; i++)
                    {
                        DataRow selected_row = UnitExchange.GetDataRow(rows[i]);
                        id_list[i] = "'" + selected_row[0].ToString() + "'";
                    }
                    string id = string.Join(",", id_list);

                    try
                    {
                        myConnection.Open();
                        string sql = "DELETE [222_PartExchange] WHERE PartNo IN (" + id + ")";
                        SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                        cmd_sql.ExecuteNonQuery();
                        cmd_sql.Dispose();
                        myConnection.Close();
                        MessageBox.Show("Unit Exchange deleted success.");
                        LoadData();
                    }
                    catch //(Exception ex)
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        //MessageBox.Show(ex.Message);
                        MessageBox.Show("Cannot delete Unit Exchange. Please try again.");
                    }
                }
            }
        }
    }
}
